package com.dinner.model;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dinner.util.PageHelper;

public class MenuItemDaoHibernate extends HibernateDaoSupport implements
		MenuItemDao {

	@SuppressWarnings("unchecked")
	@Override
	public boolean addMenuItem(String sellerID, MenuItem menuItem) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuType> menuTypeList = (java.util.List<MenuType>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuType type where type.sellerID = ? and type.menuType = ? ",
							new Object[] { sellerID, menuItem.getMenuType() });

			if (menuTypeList.size() != 1) {
				session.connection().setTransactionIsolation(isolation);
				return false;
			}

			List<MenuItem> menuItemList = (java.util.List<MenuItem>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuItem item where item.menuItemName = ? and item.menuType = ?",
							new Object[] { menuItem.getMenuItemName(),
									menuTypeList.get(0).getMenuType() });

			if (menuItemList.size() != 0) {
				session.connection().setTransactionIsolation(isolation);
				return false;
			}

			getHibernateTemplate().save(menuItem);

			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return true;

		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
			return false;
		} finally {
			session.close();
		}
	}

	@Override
	public MenuItem getMenuItemByID(int itemID) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			MenuItem item = (MenuItem) getHibernateTemplate().get(
					MenuItem.class, itemID);
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return item;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			session.close();
		}
	}

	@Override
	public boolean removeMenuItem(MenuItem menuItem, String sellerID) {
		boolean access = false;
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			MenuType menuType = (MenuType) getHibernateTemplate().get(
					MenuType.class, menuItem.getMenuType());
			if (menuType != null && menuType.getSellerID().equals(sellerID)) {
				getHibernateTemplate().delete(menuItem);
				access = true;
			}
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return access;
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
			return false;
		} finally {
			session.close();
		}
	}

/*	@SuppressWarnings("unchecked")
	@Override
	public boolean updateMenuItem(MenuItem menuItem) {
		boolean access = false;
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuItem> list = (java.util.List<MenuItem>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuItem item where item.menuItemName = ? and "
									+ "menuItemName.menuItemID = ?",
							new Object[] { menuItem.getMenuItemName(),
									menuItem.getMenuItemID() });
			if (list.isEmpty()) {
				getHibernateTemplate().update(menuItem);
				access = true;
			}
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return access;
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
			return false;
		} finally {
			session.close();
		}

	}*/

	private void saveMenuItem(MenuItem menuItem) {
		getHibernateTemplate().save(menuItem);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MenuItem> getMenuItemsByMenuType(int menuTypeID) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuItem> list = (java.util.List<MenuItem>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuItem i where i.menuType = ?",
							new Object[] { menuTypeID });
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return java.util.Collections.EMPTY_LIST;
		} finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean addMenuItem(MenuItem menuItem) {
		boolean access = false;
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuItem> list = (java.util.List<MenuItem>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuItem item where item.menuItemName = ? and "
									+ "menuItemName.menuItemID = ?",
							new Object[] { menuItem.getMenuItemName(),
									menuItem.getMenuItemID() });
			if (list.isEmpty()) {
				saveMenuItem(menuItem);
				access = true;
			}
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return access;
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
			return false;
		} finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean modifyMenuItem(String sellerId, MenuItem menuItem) {

		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuType> menuTypeList = (java.util.List<MenuType>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuType type where type.sellerID = ? and type.menuType = ? ",
							new Object[] { sellerId, menuItem.getMenuType() });

			if (menuTypeList.size() != 1) {
				session.connection().setTransactionIsolation(isolation);
				return false;
			}

			List<MenuItem> menuItemList = (java.util.List<MenuItem>) getHibernateTemplate()
					.find(
							"FROM com.dinner.model.MenuItem item where item.menuItemName = ? and item.menuType = ?",
							new Object[] { menuItem.getMenuItemName(),
									menuTypeList.get(0).getMenuType() });

			if (menuItemList.size() != 0) {
				session.connection().setTransactionIsolation(isolation);
				return false;
			}

			MenuItem menu = (MenuItem) getHibernateTemplate().get(
					MenuItem.class, menuItem.getMenuItemID());

			menu.setAvailable(menuItem.isAvailable());
			menu.setMenuItemPrice(menuItem.getMenuItemPrice());
			getHibernateTemplate().update(menu);
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			Transaction tx = session.getTransaction();
			if (tx.isActive())
				tx.rollback();
			return false;
		} finally {
			session.close();
		}
	}

	@Override
	public String getSellerByMenuID(int menuID) {
		return (String) this
				.getHibernateTemplate()
				.find(
						"SELECT DISTINCT type.sellerID FROM com.dinner.model.MenuType type, com.dinner.model.MenuItem item WHERE type.menuType = item.menuType AND item.menuItemID = ?",
						menuID).get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MenuItem> getMenuItemsByMenuTypeAndPage(final int menuTypeID,
			final PageHelper p) {
		SessionFactory sessionFactory = this.getSessionFactory();
		Session session = sessionFactory.openSession();
		int isolation = -1;
		try {
			isolation = session.connection().getTransactionIsolation();
			session.connection().setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
			session.beginTransaction();
			List<MenuItem> list = getHibernateTemplate().executeFind(
					new HibernateCallback() {
						@Override
						public Object doInHibernate(Session s)
								throws HibernateException, SQLException {
							Query query = s
									.createQuery("FROM com.dinner.model.MenuItem i where i.menuType = ?");
							query.setParameter(0, menuTypeID);
							query.setFirstResult((p.getCurrentPage() - 1)
									* p.getPageSize());
							query.setMaxResults(p.getPageSize());
							List list = query.list();
							query = s
									.createQuery("SELECT COUNT(*) FROM com.dinner.model.MenuItem i where i.menuType = "+menuTypeID);
							p
									.setTotalPage((((Integer) (query.iterate()
											.next())).intValue() - 1)
											/ p.getPageSize() + 1);

							return list;
						}

					});
			/*
			 * .find( "FROM com.dinner.model.MenuItem i where i.menuType = ?",
			 * new Object[] { menuTypeID });
			 */
			session.getTransaction().commit();
			session.connection().setTransactionIsolation(isolation);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return java.util.Collections.EMPTY_LIST;
		} finally {
			session.close();
		}
	}

}
